package com.huellas.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.huellas.bean.VacunaBean;
import com.huellas.excepcion.DAOExcepcion;
import com.huellas.util.ConexionBD;

public class VacunaDAO extends BaseDAO{
	
	//Metodo para insertar vacuna
	public void insertarVacuna(VacunaBean vacunaBean) throws DAOExcepcion {
		String query = "insert into Vacuna(NoVacuna,TxDescripcionVacuna, SsPrecioVacuna) values (?,?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, vacunaBean.getNoVacuna());
			preparedStatement.setString(2, vacunaBean.getTxDescripcionVacuna());
			preparedStatement.setDouble(3, vacunaBean.getSsPrecioVacuna());
			
			int i = preparedStatement.executeUpdate();
			
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				id = resultSet.getInt(1);
			}
			vacunaBean.setCoVacuna(id);

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
	}
	
	//Metodo para buscar vacunas
	public Collection<VacunaBean> buscarVacuna(String NoVacuna, String TxDescripcionVacuna) throws DAOExcepcion{
		String query = "select NoVacuna, TxDescripcionVacuna, SsPrecioVacuna from Vacuna where NoVacuna like ? and TxDescripcionVacuna like ?";
		Collection<VacunaBean> listaVacuna = new ArrayList<VacunaBean>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "%" + NoVacuna + "%");
			preparedStatement.setString(2, "%" + TxDescripcionVacuna + "%");
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				VacunaBean vacunaBean = new VacunaBean();
				vacunaBean.setNoVacuna(resultSet.getString("NoVacuna"));
				vacunaBean.setTxDescripcionVacuna(resultSet.getString("TxDescripcionVacuna"));
				vacunaBean.setSsPrecioVacuna(resultSet.getDouble("SsPrecioVacuna"));
				listaVacuna.add(vacunaBean);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return listaVacuna;
	}
	
	//Metodo para buscar vacunas
	public Collection<VacunaBean> listarNoVacuna() throws DAOExcepcion{
		String query = "select NoVacuna from Vacuna";
		Collection<VacunaBean> listaVacuna = new ArrayList<VacunaBean>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				VacunaBean vacunaBean = new VacunaBean();
				vacunaBean.setNoVacuna(resultSet.getString("NoVacuna"));
				listaVacuna.add(vacunaBean);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
			
		return listaVacuna;
	}
	
	//Metodo para obtener registro de la tabla vacuna
	public VacunaBean obtenerRegistroVacuna(String NoVacuna, double SsPrecioVacuna) throws DAOExcepcion{
		VacunaBean vacunaBean = new VacunaBean();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select CoVacuna, NoVacuna, TxDescripcionVacuna, SsPrecioVacuna from Vacuna where NoVacuna=? and SsPrecioVacuna=?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, NoVacuna);
			preparedStatement.setDouble(2, SsPrecioVacuna);
			resultSet = preparedStatement.executeQuery();
			
			if(resultSet.next()){
				vacunaBean.setCoVacuna(resultSet.getInt("CoVacuna"));
				vacunaBean.setNoVacuna(resultSet.getString("NoVacuna"));
				vacunaBean.setTxDescripcionVacuna(resultSet.getString("TxDescripcionVacuna"));
				vacunaBean.setSsPrecioVacuna(resultSet.getDouble("SsPrecioVacuna"));
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return vacunaBean;
	}
	
	//Metodo para obtener el codigo de vacuna
	public int obtenerCoVacuna(String NoVacuna) throws DAOExcepcion{
		int coVacuna = 0;
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select CoVacuna from Vacuna where NoVacuna=?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, NoVacuna);
			resultSet = preparedStatement.executeQuery();
			
			if(resultSet.next()){
				coVacuna = resultSet.getInt(1);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return coVacuna;
	}
}
